package com.example.teacherservice.repository;

import com.example.teacherservice.entity.Chapter;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.web.bind.annotation.PathVariable;

import java.util.List;
import java.util.Optional;

public interface ChapterRepository extends JpaRepository<Chapter, Long> {

    @Query("SELECT c FROM Chapter c WHERE c.courseId = :courseId AND c.deleted = 0")
    List<Chapter> findByCourseId(@Param("courseId") Long courseId);

    @Query("SELECT c.id FROM Chapter c WHERE c.courseId = :courseId AND c.deleted = 0")
    List<Long> findIdsByCourseId(@Param("courseId") Long courseId);

    @Query("SELECT MAX(c.sortOrder) FROM Chapter c WHERE c.courseId = :courseId AND c.deleted = 0")
    Optional<Integer> findMaxSortOrderByCourseId(@Param("courseId") Long courseId);

    @Query("SELECT c FROM Chapter c WHERE c.courseId = :courseId AND c.index = :index AND c.deleted = 0")
    Optional<Chapter> findByCourseIdAndIndex(@Param("courseId") Long courseId, @Param("index") Integer index);

    // 新增计数方法
    @Query("SELECT COUNT(c) FROM Chapter c WHERE c.courseId = :courseId AND c.deleted = :deleted")
    long countByCourseIdAndDeleted(@Param("courseId") Long courseId, @Param("deleted") int deleted);

    @Modifying
    @Transactional
    @Query("UPDATE Chapter c SET c.deleted = 1 WHERE c.id = :chapterId")
    void softDelete(@Param("chapterId") Long chapterId);

    @Query("SELECT c FROM Chapter c WHERE c.courseId = :courseId AND c.deleted = :deleted ORDER BY c.sortOrder")
    List<Chapter> findByCourseIdAndDeletedOrderBySortOrder(
            @Param("courseId") Long courseId,
            @Param("deleted") Integer deleted);

        @Query("SELECT MAX(c.index) FROM Chapter c WHERE c.courseId = :courseId")
        Optional<Integer> findMaxIndexByCourseId(@PathVariable Long courseId);

    // 新增：根据课程ID删除所有章节
    @Modifying
    @Query("DELETE FROM Chapter c WHERE c.courseId = :courseId")
    void deleteByCouId(@Param("courseId") Long courseId);

    // 根据课程ID查询章节
    List<Chapter> findByCourseIdAndDeleted(Long courseId, Integer deleted);

    @Modifying
    @Query("UPDATE Chapter c SET c.duration = :duration WHERE c.id = :id")
    void updateDuration(@Param("id") Long id, @Param("duration") Long duration);

    /**
     * 根据课程ID删除所有章节（物理删除）
     * @param courseId 课程ID
     */
    @Modifying
    @Transactional
    @Query("DELETE FROM Chapter c WHERE c.courseId = :courseId")
    void deleteByCourseId(@Param("courseId") Long courseId);


}